CAdoConnection Class

Represents an open connection to a data source.

Include file: CAdoConnection.inc (include CADODB.inc)

A Connection object represents a unique session with a data source. In the case of a client/server database system, it may be equivalent to an actual network connection to the server. Depending on the functionality supported by the provider, some collections, methods, or properties of a Connection object may not be available.

With the collections, methods, and properties of a Connection object, you can do the following:

• Configure the connection before opening it with the ConnectionString, ConnectionTimeout, and Mode properties. ConnectionString is the default property of the Connection object.
• Set the CursorLocation property to client to invoke the Microsoft Cursor Service for OLE DB, which supports batch updates.
• Set the default database for the connection with the DefaultDatabase property.
• Set the level of isolation for the transactions opened on the connection with the IsolationLevel property.
• Specify an OLE DB provider with the Provider property.
• Establish, and later break, the physical connection to the data source with the Open and Close methods.
• Execute a command on the connection with the Execute method and configure the execution with the CommandTimeout property.
• Manage transactions on the open connection, including nested transactions if the provider supports them, with the BeginTrans, CommitTrans, and RollbackTrans methods and the Attributes property.
• Examine errors returned from the data source with the Errors collection.
• Read the version from the ADO implementation used with the Version property.
• Obtain schema information about your database with the OpenSchema method.

You can create Connection objects independently of any other previously defined object.

To execute a query without using a Command object, pass a query string to the Execute method of a Connection object. However, a Command object is required when you want to persist the command text and re-execute it, or use query parameters.

Methods and Properties

Connection Dynamic Properties

The table below is a cross-index of the ADO and OLE DB names for each standard OLE DB provider dynamic property. Your providers may add more properties than listed here. For the specific information about provider-specific dynamic properties, see your provider documentation.

Attributes

Indicates one or more characteristics of an object.

PROPERTY Attributes () AS LONG
PROPERTY Attributes (BYVAL lAttr AS LONG)

Return value

One of the following values.

XactAttributeEnum

Specifies the transaction attributes of a Connection object.

Remarks

Use the Attributes property to set or return characteristics of Connection objects.

When you set multiple attributes, you can sum the appropriate constants. If you set the property value to a sum including incompatible constants, an error occurs.

Remote Data Service Usage

This property is not available on a client-side Connection object.

BeginTrans

Begins a new transaction.

FUNCTION BeginTrans () AS LONG

Return value

The nesting level of the transaction.

Remarks

Use BeginTrans, CommitTrans and RollbackTrans with a Connection object when you want to save or cancel a series of changes made to the source data as a single unit. For example, to transfer money between accounts, you subtract an amount from one and add the same amount to the other. If either update fails, the accounts no longer balance. Making these changes within an open transaction ensures that either all or none of the changes go through.

Not all providers support transactions. Verify that the provider-defined property “Transaction DDL” appears in the Connection object’s Properties collection, indicating that the provider supports transactions. If the provider does not support transactions, calling one of these methods will return an error.

After you call the BeginTrans method, the provider will no longer instantaneously commit changes you make until you call CommitTrans or RollbackTrans to end the transaction.

For providers that support nested transactions, calling the BeginTrans method within an open transaction starts a new, nested transaction. The return value indicates the level of nesting: a return value of “1” indicates you have opened a top-level transaction (that is, the transaction is not nested within another transaction), “2” indicates that you have opened a second-level transaction (a transaction nested within a top-level transaction), and so forth. Calling CommitTrans or RollbackTrans affects only the most recently opened transaction; you must close or roll back the current transaction before you can resolve any higher-level transactions.

Calling the CommitTrans method saves changes made within an open transaction on the connection and ends the transaction. Calling the RollbackTrans method reverses any changes made within an open transaction and ends the transaction. Calling either method when there is no open transaction generates an error.

Depending on the Connection object’s Attributes property, calling either the CommitTrans or RollbackTrans methods may automatically start a new transaction. If the Attributes property is set to adXactCommitRetaining, the provider automatically starts a new transaction after a CommitTrans call. If the Attributes property is set to adXactAbortRetaining, the provider automatically starts a new transaction after a RollbackTrans call.

Remote Data Service

The BeginTrans, CommitTrans, and RollbackTrans methods are not available on a client-side Connection object.

Example

#include once "AfxNova/CADODB.inc"
USING AfxNova

' // === Open the connection
DIM pConnection AS CAdoConnection
pConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb")

' // === Open the recordset
DIM pRecordset AS CAdoRecordset
pRecordset.Open("SELECT * FROM Authors", pConnection, adOpenKeyset, adLockOptimistic, adCmdText)

' // === Begin a transaction
pConnection.BeginTrans

' // === Parse the recordset
' // While not at the end of the recordset...
DO WHILE NOT pRecordset.EOF
   ' // Get the content of the "Author" column
   DIM dvRes AS DVARIANT = pRecordset.Collect("Year Born")
   IF VAL(dvRes) = 1947 THEN pRecordset.Collect("Year Born") = 1900
   ' // Fetch the next row
   IF pRecordset.MoveNext <> S_OK THEN EXIT DO
LOOP

' // === Commit the transaction
'pConnection.CommitTrans
' // === Rollback the transaction because this is a demo
pConnection.RollbackTrans

' // === Close the recordset and the connection
' // If you don't close them, they will be closed when the application ends
pRecordset.Close
pConnection.Close

Cancel

Cancels execution of a pending, asynchronous method call.

FUNCTION Cancel () AS HRESULT

Return value

S_OK (0) or an HRESULT code.

Remarks

Use the Cancel method to terminate execution of an asynchronous method call (that is, a method invoked with the adAsyncConnect, adAsyncExecute, or adAsyncFetch option).

For a Connection object, the last asynchronous call to the Execute or Open methods is terminated.

Close

Closes a Connection object and any dependent objects.

FUNCTION Close () AS HRESULT

Return value

S_OK (0) or an HRESULT code.

Remarks

Use the Close method to close a Connection to free any associated system resources. Closing an object does not remove it from memory; you can change its property settings and open it again later. To completely eliminate an object from memory, release the connection calling the Release method of the interface.

Usage example

IF pConnection.State = adStateOpen THEN pConnection.Close

…

CommandTimeout

Sets or returns a Long value that indicates, in seconds, how long to wait for a command to execute. Default is 30.

PROPERTY CommandTimeout () AS LONG
PROPERTY CommandTimeout (BYVAL lTimeout AS LONG)

Return value

The timeout value.

Remarks

Use the CommandTimeout property on a Connection object to allow the cancellation of an Execute method call, due to delays from network traffic or heavy server use. If the interval set in the CommandTimeout property elapses before the command completes execution, an error occurs and ADO cancels the command. If you set the property to zero, ADO will wait indefinitely until the execution is complete. Make sure the provider and data source to which you are writing code support the CommandTimeout functionality.

The CommandTimeout setting on a Connection object has no effect on the CommandTimeout setting on a Command object on the same Connection; that is, the Command object’s CommandTimeout property does not inherit the value of the Connection object’s CommandTimeout value.

On a Connection object, the CommandTimeout property remains read/write after the Connection is opened.

Examples

' // Sets the timeout to 35 seconds
pConnection.CommandTimeout = 35

' // Gets the timeout value
DIM lTimeout AS LONG = pConnection.CommandTimeout

CommitTrans

Saves any changes and ends the current transaction. It may also start a new transaction.

FUNCTION CommitTrans () AS HRESULT

Return value

S_OK (0) or an HRESULT code.

Remarks

Use BeginTrans, CommitTrans and RollbackTrans with a Connection object when you want to save or cancel a series of changes made to the source data as a single unit. For example, to transfer money between accounts, you subtract an amount from one and add the same amount to the other. If either update fails, the accounts no longer balance. Making these changes within an open transaction ensures that either all or none of the changes go through.

Not all providers support transactions. Verify that the provider-defined property “Transaction DDL” appears in the Connection object’s Properties collection, indicating that the provider supports transactions. If the provider does not support transactions, calling one of these methods will return an error.

After you call the BeginTrans method, the provider will no longer instantaneously commit changes you make until you call CommitTrans or RollbackTrans to end the transaction.

For providers that support nested transactions, calling the BeginTrans method within an open transaction starts a new, nested transaction. The return value indicates the level of nesting: a return value of “1” indicates you have opened a top-level transaction (that is, the transaction is not nested within another transaction), “2” indicates that you have opened a second-level transaction (a transaction nested within a top-level transaction), and so forth. Calling CommitTrans or RollbackTrans affects only the most recently opened transaction; you must close or roll back the current transaction before you can resolve any higher-level transactions.

Calling the CommitTrans method saves changes made within an open transaction on the connection and ends the transaction. Calling the RollbackTrans method reverses any changes made within an open transaction and ends the transaction. Calling either method when there is no open transaction generates an error.

Depending on the Connection object’s Attributes property, calling either the CommitTrans or RollbackTrans methods may automatically start a new transaction. If the Attributes property is set to adXactCommitRetaining, the provider automatically starts a new transaction after a CommitTrans call. If the Attributes property is set to adXactAbortRetaining, the provider automatically starts a new transaction after a RollbackTrans call.

Remote Data Service

The BeginTrans, CommitTrans, and RollbackTrans methods are not available on a client-side Connection object.

Example

#include once "AfxNova/CADODB.inc"
USING AfxNova

' // === Open the connection
DIM pConnection AS CAdoConnection
pConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb")

' // === Open the recordset
DIM pRecordset AS CAdoRecordset
pRecordset.Open("SELECT * FROM Authors", pConnection, adOpenKeyset, adLockOptimistic, adCmdText)

' // === Begin a transaction
pConnection.BeginTrans

' // === Parse the recordset
' // While not at the end of the recordset...
DO WHILE NOT pRecordset.EOF
   ' // Get the content of the "Author" column
   DIM dvRes AS DVARIANT = pRecordset.Collect("Year Born")
   IF VAL(dvRes) = 1947 THEN pRecordset.Collect("Year Born") = 1900
   ' // Fetch the next row
   IF pRecordset.MoveNext <> S_OK THEN EXIT DO
LOOP

' // === Commit the transaction
'pConnection.CommitTrans
' // === Rollback the transaction because this is a demo
pConnection.RollbackTrans

' // === Close the recordset and the connection
' // If you don't close them, they will be closed when the application ends
pRecordset.Close
pConnection.Close

ConnectionString

Indicates the information used to establish a connection to a data source.

PROPERTY ConnectionString () AS DWSTRING
PROPERTY ConnectionString (BYREF wszConStr AS WSTRING)

Return value

The connection string.

Remarks

Use the ConnectionString property to specify a data source by passing a detailed connection string containing a series of argument = value statements separated by semicolons.

ADO supports five arguments for the ConnectionString property; any other arguments pass directly to the provider without any processing by ADO. The arguments ADO supports are as follows.

After you set the ConnectionString property and open the Connection object, the provider may alter the contents of the property, for example, by mapping the ADO-defined argument names to their provider equivalents.

The ConnectionString property automatically inherits the value used for the ConnectionString argument of the Open method, so you can override the current ConnectionString property during the Open method call.

Because the File Name argument causes ADO to load the associated provider, you cannot pass both the Provider and File Name arguments.

The ConnectionString property is read/write when the connection is closed and read-only when it is open.

Duplicates of an argument in the ConnectionString property are ignored. The last instance of any argument is used.

Remote Data Service Usage

When used on a client-side Connection object, the ConnectionString property can include only the Remote Provider and Remote Server parameters.

Usage examples

pConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
pConnection.Open

DIM dwsConStr AS DWSTRING = pConnection.ConnectionString

Example

#include once "AfxNova/CADODB.inc"
USING AfxNova

' // === Open the connection
DIM pConnection AS CAdoConnection
pConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
pConnection.Open

' // === Open the recordset
DIM pRecordset AS CAdoRecordset
pRecordset.Open("SELECT * FROM Authors", pConnection, adOpenKeyset, adLockOptimistic, adCmdText)

' // === Parse the recordset
' // While not at the end of the recordset...
DO WHILE NOT pRecordset.EOF
   ' // Get the content of the "Author" column
   PRINT pRecordset.Collect("Author")
   ' // Fetch the next row
   IF pRecordset.MoveNext <> S_OK THEN EXIT DO
LOOP

' // === Close the recordset and the connection
' // If you don't close them, they will be closed when the application ends
pRecordset.Close
pConnection.Close

ConnectionTimeout

Indicates how long to wait while establishing a connection before terminating the attempt and generating an error.

PROPERTY ConnectionTimeout () AS LONG
PROPERTY ConnectionTimeout (BYVAL lTimeout AS LONG)

Return value

The timeout value.

Remarks

Use the ConnectionTimeout property on a Connection object if delays from network traffic or heavy server use make it necessary to abandon a connection attempt. If the time from the ConnectionTimeout property setting elapses prior to the opening of the connection, an error occurs and ADO cancels the attempt. If you set the property to zero, ADO will wait indefinitely until the connection is opened. Make sure the provider to which you are writing code supports the ConnectionTimeout functionality.

The ConnectionTimeout property is read/write when the connection is closed and read-only when it is open.

On a Connection object, the ConnectionTimeout property remains read/write after the Connection is opened.

CursorLocation

Indicates the location of the cursor service.

PROPERTY CursorLocation () AS CursorLocationEnum
PROPERTY CursorLocation (BYVAL lCursorLoc AS CursorLocationEnum)

Return value

One of the CursorLocationEnum values.

CursorLocationEnum

Specifies the location of the cursor service.

Remarks

This property allows you to choose between various cursor libraries accessible to the provider. Usually, you can choose between using a client-side cursor library or one that is located on the server.

This property setting affects connections established only after the property has been set. Changing the CursorLocation property has no effect on existing connections.

Cursors returned by the Execute method inherit this setting. Recordset objects will automatically inherit this setting from their associated connections.

This property is read/write on a Connection or a closed Recordset, and read-only on an open Recordset.

Remote Data Service Usage

When used on a client-side Recordset or Connection object, the CursorLocation property can only be set to adUseClient.

Examples

' // Sets the cursor location
pConnection.CursorLocation = adUseClient

' // Gets the timeout value
DIM lCursorLoc AS LONG = pConnection.CursorLocation

DefaultDatabase

Indicates the default database for a Connection object.

PROPERTY DefaultDatabase () AS DWsTRING
PROPERTY DefaultDatabase (BYREF wszDatabase AS WSTRING)

Return value

The name of the database.

Remarks

Use the DefaultDatabase property to set or return the name of the default database on a specific Connection object.

If there is a default database, SQL strings may use an unqualified syntax to access objects in that database. To access objects in a database other than the one specified in the DefaultDatabase property, you must qualify object names with the desired database name. Upon connection, the provider will write default database information to the DefaultDatabase property. Some providers allow only one database per connection, in which case you cannot change the DefaultDatabase property.

Some data sources and providers may not support this feature, and may return an error or an empty string.

Remote Data Service Usage

This property is not available on a client-side Connection object.

Execute

Executes the specified query, SQL statement, stored procedure, or provider-specific text.

FUNCTION Execute (BYREF wszCommandText AS WSTRING, BYVAL RecordsAffected AS LONG PTR = NULL, _
   BYVAL Options AS LONG = -1) AS Afx_ADORecordset PTR

Use the ExecuteOptionEnum value adExecuteNoRecords to improve performance by minimizing internal processing.

Do not use adExecuteStream with the Execute method of a Connection object.

Do not use the CommandTypeEnum values of adCmdFile or adCmdTableDirect with Execute. These values can only be used as options with the Open and Requery methods of a Recordset.

CommandTypeEnum

Specifies how a command argument should be interpreted.

ExecuteOptionEnum

Specifies how a provider should execute a command.

Return value

An Afx_ADORecordset object reference on success, or NULL on failure.

Remarks

Using the Execute method on a Connection object executes whatever query you pass to the method in the CommandText argument on the specified connection. If the CommandText argument specifies a row-returning query, any results that the execution generates are stored in a new Recordset object. If the command is not intended to return results (for example, an SQL UPDATE query) the provider returns Nothing as long as the option adExecuteNoRecords is specified; otherwise Execute returns a closed Recordset.

The returned Recordset object is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, first create a Recordset object with the desired property settings, then use the Recordset object’s Open method to execute the query and return the desired cursor type.

The contents of the CommandText argument are specific to the provider and can be standard SQL syntax or any special command format that the provider supports.

An ExecuteComplete event will be issued when this operation concludes.

Note: URLs using the http scheme will automatically invoke the Microsoft OLE DB Provider for Internet Publishing.

Example

#include "AfxNova/CADODB.inc"
USING AfxNova

' // === Open the connection
DIM pConnection AS CAdoConnection
pConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"

' // == Create a Command object
DIM pCommand AS CAdoCommand

' // === Set the active connection
pCommand.ActiveConnection = pConnection

' // === Set the CommandText property
pCommand.CommandText = "SELECT TOP 20 * FROM Authors ORDER BY Author"

' // === Create the recordset by executing a query and attaching
' // === the resulting recordset to an instance of the CAdoRecordset class.
DIM pRecordset AS CAdoRecordset = pCommand.Execute

' // === Parse the recordset
' // While not at the end of the recordset...
DO WHILE NOT pRecordset.EOF
   ' // Get the content of the "Author" column
   PRINT pRecordset.Collect("Author")
   ' // Fetch the next row
   IF pRecordset.MoveNext <> S_OK THEN EXIT DO
LOOP

' // === Close the recordset and the connection
' // If you don't close them, they will be closed when the application ends
pRecordset.Close
pConnection.Close

GetErrorInfo

Returns information about ADO errors.

FUNCTION GetErrorInfo (BYVAL nError AS HRESULT = 0) AS DWSTRING

Return value

A description of the error(s).

IsolationLevel

Indicates the level of isolation for a Connection object.

PROPERTY IsolationLevel () AS IsolationLevelEnum
PROPERTY IsolationLevel (BYVAL Level AS IsolationLevelEnum)

Return value

An IsolationLevelEnum value.

IsolationLevelEnum

Specifies the level of transaction isolation for a Connection object.

Remarks

Use the IsolationLevel property to set the isolation level of a Connection object. The setting does not take effect until the next time you call the BeginTrans method. If the level of isolation you request is unavailable, the provider may return the next greater level of isolation without updating the IsolationLevel property.

Remote Data Service Usage

When used on a client-side Connection object, the IsolationLevel property can be set only to adXactUnspecified.

Because users are working with disconnected Recordset objects on a client-side cache, there may be multiuser issues. For instance, when two different users try to update the same record, Remote Data Service simply allows the user who updates the record first to “win.” The second user’s update request will fail with an error.

Examples

' // Sets the isolation level
pConnection.IsolationLevel = adXactUnspecified

' // Gets the isolation level
DIM level AS LONG = pConnection.IsolationLevel

Mode

Indicates the available permissions for modifying data in a Connection object.

PROPERTY Mode () AS ConnectModeEnum
PROPERTY Mode (BYVAL lMode AS ConnectModeEnum)

Return value

A ConnectModeEnum value.

ConnectModeEnum

Specifies the available permissions for modifying data in a Connection, opening a Record, or specifying values for the Mode property of the Record and Stream objects.

Open

Opens a connection to a data source.

FUNCTION Open (BYREF wszConStr AS WSTRING = "", BYREF wszUserID AS WSTRING = "", _
   BYREF wszPassword AS WSTRING = "", BYVAL Options AS LONG = adOptionUnspecified) AS HRESULT

ConnectOptionEnum

Specifies whether the Open method of a Connection object should return after (synchronously) or before (asynchronously) the connection is established.

Return value

S_OK (0) or an HRESULT code.

Remarks

Using the Open method on a Connection object establishes the physical connection to a data source. After this method successfully completes, the connection is live and you can issue commands against it and process the results.

Use the optional ConnectionString argument to specify either a connection string containing a series of argument = value statements separated by semicolons, or a file or directory resource identified with a URL. The ConnectionString property automatically inherits the value used for the ConnectionString argument. Therefore, you can either set the ConnectionString property of the Connection object before opening it, or use the ConnectionString argument to set or override the current connection parameters during the Open method call.

If you pass user and password information both in the ConnectionString argument and in the optional UserID and Password arguments, the UserID and Password arguments will override the values specified in ConnectionString.

When you have concluded your operations over an open Connection, use the Close method to free any associated system resources. Closing an object does not remove it from memory; you can change its property settings and use the Open method to open it again later. To completely eliminate an object from memory, release the object variable.

Remote Data Service Usage

When used on a client-side Connection object, the Open method doesn’t actually establish a connection to the server until a Recordset is opened on the Connection object.

Note: URLs using the http scheme will automatically invoke the Microsoft OLE DB Provider for Internet Publishing.

Example

' // Set the connection string
pConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
' // Open the connection
pConnection.Open

Example

#include once "AfxNova/CADODB.inc"
USING AfxNova

' // === Open the connection
DIM pConnection AS CAdoConnection
pConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb")

' // === Open the recordset
DIM pRecordset AS CAdoRecordset
pRecordset.Open("SELECT * FROM Authors", pConnection, adOpenKeyset, adLockOptimistic, adCmdText)

' // === Parse the recordset
' // While not at the end of the recordset...
DO WHILE NOT pRecordset.EOF
   ' // Get the content of the "Author" column
   PRINT pRecordset.Collect("Author")
   ' // Fetch the next row
   IF pRecordset.MoveNext <> S_OK THEN EXIT DO
LOOP

' // === Close the recordset and the connection
' // If you don't close them, they will be closed when the application ends
pRecordset.Close
pConnection.Close

OpenSchema

Obtains database schema information from the provider.

FUNCTION OpenSchema (BYVAL Schema AS SchemaEnum, _
   BYVAL Restrictions AS VARIANT = TYPE(VT_ERROR,0,0,0,DISP_E_PARAMNOTFOUND), _
   BYVAL SchemaID AS VARIANT = TYPE(VT_ERROR,0,0,0,DISP_E_PARAMNOTFOUND)) AS Afx_ADORecordset PTR

Return value

An ADO Recordset object reference.

Remarks

The OpenSchema method returns self-descriptive information about the data source, such as what tables are in the data source, the columns in the tables, and the data types supported.

The QueryType argument is a GUID that indicates the columns (schemas) returned. The OLE DB specification has a full list of schemas.

The Criteria argument limits the results of a schema query. Criteria specifies an array of values that must occur in a corresponding subset of columns, called constraint columns, in the resulting Recordset.

The constant adSchemaProviderSpecific is used for the QueryType argument if the provider defines its own nonstandard schema queries outside those listed above. When this constant is used, the SchemaID argument is required to pass the GUID of the schema query to execute. If QueryType is set to adSchemaProviderSpecific but SchemaID is not provided, an error will result.

Providers are not required to support all of the OLE DB standard schema queries. Specifically, only adSchemaTables, adSchemaColumns, and adSchemaProviderTypes are required by the OLE DB specification. However, the provider is not required to support the Criteria constraints listed above for those schema queries.

Remote Data Service Usage

The OpenSchema method is not available on a client-side Connection object.

When not using client side cursors, retrieving the “ORDINAL_POSITION” of a column schema in ADO returns a variant of type VT_R8 in MDAC 2.7 and later while the type of used in MDAC 2.6 was VT_I4. Programs written for MDAC 2.6 that only look for a variant returned of type VT_I4 would get a zero for every ordinal if run under MDAC 2.7 and later without modification. This change was made because the data type that OLE DB returns is DBTYPE_UI4, and in the signed VT_I4 type there is not enough room to contain all possible values without possibly truncation occurring and thereby causing a loss of data.

Properties

Returns a reference to the Properties collection.

PROPERTY Properties () AS Afx_ADOProperties PTR

Return value

An Afx_ADOProperties object reference.

Usage example

DIM pProperties AS Afx_ADOProperties
pProperties = pConnection.Properties

Provider

Indicates the name of the provider for a Connection object.

PROPERTY Provider () AS DWSTRING
PROPERTY Provider (BYREF wszProvider AS WSTRING)

Return value

The provider name.

Remarks

Use the Provider property to set or return the name of the provider for a connection. This property can also be set by the contents of the ConnectionString property or the ConnectionString argument of the Open method; however, specifying a provider in more than one place while calling the Open method can have unpredictable results. If no provider is specified, the property will default to MSDASQL (Microsoft OLE DB Provider for ODBC).

The Provider property is read/write when the connection is closed and read-only when it is open. The setting does not take effect until you either open the Connection object or access the Properties collection of the Connection object. If the setting is not valid, an error occurs.

Usage example

DIM dwsProvider AS DWSTRING = pConnection.Provider

RollbackTrans

Saves any changes and ends the current transaction. It may also start a new transaction.

FUNCTION RollbackTrans () AS HRESULT

Return value

S_OK (0) or an HRESULT code.

Use BeginTrans, CommitTrans and RollbackTrans with a Connection object when you want to save or cancel a series of changes made to the source data as a single unit. For example, to transfer money between accounts, you subtract an amount from one and add the same amount to the other. If either update fails, the accounts no longer balance. Making these changes within an open transaction ensures that either all or none of the changes go through.

Not all providers support transactions. Verify that the provider-defined property “Transaction DDL” appears in the Connection object’s Properties collection, indicating that the provider supports transactions. If the provider does not support transactions, calling one of these methods will return an error.

After you call the BeginTrans method, the provider will no longer instantaneously commit changes you make until you call CommitTrans or RollbackTrans to end the transaction.

For providers that support nested transactions, calling the BeginTrans method within an open transaction starts a new, nested transaction. The return value indicates the level of nesting: a return value of “1” indicates you have opened a top-level transaction (that is, the transaction is not nested within another transaction), “2” indicates that you have opened a second-level transaction (a transaction nested within a top-level transaction), and so forth. Calling CommitTrans or RollbackTrans affects only the most recently opened transaction; you must close or roll back the current transaction before you can resolve any higher-level transactions.

Calling the CommitTrans method saves changes made within an open transaction on the connection and ends the transaction. Calling the RollbackTrans method reverses any changes made within an open transaction and ends the transaction. Calling either method when there is no open transaction generates an error.

Depending on the Connection object’s Attributes property, calling either the CommitTrans or RollbackTrans methods may automatically start a new transaction. If the Attributes property is set to adXactCommitRetaining, the provider automatically starts a new transaction after a CommitTrans call. If the Attributes property is set to adXactAbortRetaining, the provider automatically starts a new transaction after a RollbackTrans call.

Remote Data Service

The BeginTrans, CommitTrans, and RollbackTrans methods are not available on a client-side Connection object.

Example

#include once "AfxNova/CADODB.inc"
USING AfxNova

' // === Open the connection
DIM pConnection AS CAdoConnection
pConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb")

' // === Open the recordset
DIM pRecordset AS CAdoRecordset
pRecordset.Open("SELECT * FROM Authors", pConnection, adOpenKeyset, adLockOptimistic, adCmdText)

' // === Begin a transaction
pConnection.BeginTrans

' // === Parse the recordset
' // While not at the end of the recordset...
DO WHILE NOT pRecordset.EOF
   ' // Get the content of the "Author" column
   DIM dvRes AS DVARIANT = pRecordset.Collect("Year Born")
   IF VAL(dvRes) = 1947 THEN pRecordset.Collect("Year Born") = 1900
   ' // Fetch the next row
   IF pRecordset.MoveNext <> S_OK THEN EXIT DO
LOOP

' // === Commit the transaction
'pConnection.CommitTrans
' // === Rollback the transaction because this is a demo
pConnection.RollbackTrans

' // === Close the recordset and the connection
' // If you don't close them, they will be closed when the application ends
pRecordset.Close
pConnection.Close

State

Indicates if a Connection is open or closed.

PROPERTY State () AS LONG

Return value

The current connection state.

Remarks

You can use the State property to determine the current state of a given object at any time.

The object’s State property can have a combination of values. For example, if a statement is executing, this property will have a combined value of adStateOpen and adStateExecuting.

ObjectStateEnum

Specifies whether the Open method of a Connection object should return after (synchronously) or before (asynchronously) the connection is established.

Usage example

IF pConnection.State = adStateOpen THEN pConnection.Close

Version

Indicates the ADO version number.

PROPERTY Version () AS DWSTRING

Return value

The ADO version number.

Remarks

Use the Version property to return the version number of the ADO implementation.

The version of the provider will be available as a dynamic property in the Properties collection.

Example

DIM dwsVersion AS DWSTRING = pConnection.Version

SetEvents

Sets the events sink.

FUNCTION SetEvents (BYVAL pEvtSink AS ANY PTR) AS HRESULT

Return value

Returns S_OK (0) if successful or an error value otherwise.